﻿using System;
using System.Collections.Concurrent;
using System.Collections.Generic;
using System.Linq;
using System.Data;
using System.Data.Common;
using System.Reflection;
using System.Text;
using System.Web;
using StackExchange.DataExplorer.Helpers;
using Dapper;

namespace StackExchange.DataExplorer.Models
{
    /// <summary>
    /// Extends Dapper.Database so we don't have to mess up Dapper.Rainbow code with our
    /// multi-DB SQL code.
    /// </summary>
    /// <typeparam name="TDatabase"></typeparam>
    public abstract class DataExplorerDatabaseHelper<TDatabase> : Dapper.Database<TDatabase> where TDatabase : DataExplorerDatabaseHelper<TDatabase>, new()
    {
        /// <summary>
        /// This is messy, but necessary to avoid modifying Dapper.Rainbow code directly.
        /// However, it's probably worthwhile to consider modifying Dapper.Rainbow to make
        /// these kinds of drop-ins a little easier.
        /// </summary>
        /// <typeparam name="T"></typeparam>
        public class DataExplorerTable<T> : Table<T, int>
        {
            new DataExplorerDatabaseHelper<TDatabase> database;

            public DataExplorerTable(DataExplorerDatabaseHelper<TDatabase> database, string likelyTableName) : base(database, likelyTableName)
            {
                this.database = database;
            }

            public override int? Insert(dynamic data)
            {
                var o = (object)data;
                List<string> names = GetParamNames(o);

                string columns = names.Select(p => database.Dialect.Quote(p)).Join(",");
                string placeholders = names.Select(p => "@" + p).Join(",");

                var sql = string.Format(database.Dialect.GetQuery("_Database.Helper.Insert", @"
                    SET NOCOUNT ON INSERT INTO {0} ({1}) VALUES({2})
                    SELECT CAST(SCOPE_IDENTITY() AS int)"
                ), TableName, columns, placeholders);

                return database.Query<int?>(sql, o).Single();
            }

            public override T First()
            {
                var sql = string.Format(database.Dialect.GetQuery("_Database.Helper.First", @"SELECT TOP 1 * FROM {0}"), TableName);

                return database.Query<T>(sql).FirstOrDefault();
            }
        }

        public SqlDialect Dialect { get; private set; }
        public DbConnection RawConnection { get; private set; }

        public static TDatabase Init(DbConnection connection, string providerName, int commandTimeout)
        {
            var db = Init(connection, commandTimeout);
            db.Dialect = SqlDialect.GetDialect(SqlDialect.Guess(providerName));
            db.RawConnection = connection;

            return db;
        }

        internal override Action<TDatabase> CreateTableConstructorForTable()
        {
            return CreateTableConstructor(typeof(DataExplorerTable<>));
        }

        public int Execute(string name, string sql, dynamic param = null)
        {
            // cast param here and in subsequent methods to avoid dynamic calls back to ourself
            return Execute(Dialect.GetQuery(name, sql), (object)param);
        }

        public IEnumerable<T> Query<T>(string name, string sql, dynamic param = null, bool buffered = true)
        {
            return Query<T>(Dialect.GetQuery(name, sql), (object)param, buffered);
        }

        public IEnumerable<TReturn> Query<TFirst, TSecond, TReturn>(string name, string sql, Func<TFirst, TSecond, TReturn> map, dynamic param = null, IDbTransaction transaction = null, bool buffered = true, string splitOn = "Id", int? commandTimeout = null)
        {
            return Query<TFirst, TSecond, TReturn>(Dialect.GetQuery(name, sql), map, (object)param, transaction, buffered, splitOn, commandTimeout);
        }

        public IEnumerable<TReturn> Query<TFirst, TSecond, TThird, TReturn>(string name, string sql, Func<TFirst, TSecond, TThird, TReturn> map, dynamic param = null, IDbTransaction transaction = null, bool buffered = true, string splitOn = "Id", int? commandTimeout = null)
        {
            return Query<TFirst, TSecond, TThird, TReturn>(Dialect.GetQuery(name, sql), map, (object)param, transaction, buffered, splitOn, null);
        }

        public IEnumerable<TReturn> Query<TFirst, TSecond, TThird, TFourth, TReturn>(string name, string sql, Func<TFirst, TSecond, TThird, TFourth, TReturn> map, dynamic param = null, IDbTransaction transaction = null, bool buffered = true, string splitOn = "Id", int? commandTimeout = null)
        {
            return Query<TFirst, TSecond, TThird, TFourth, TReturn>(Dialect.GetQuery(name, sql), map, (object)param, transaction, buffered, splitOn, null);
        }

        public IEnumerable<TReturn> Query<TFirst, TSecond, TThird, TFourth, TFifth, TReturn>(string name, string sql, Func<TFirst, TSecond, TThird, TFourth, TFifth, TReturn> map, dynamic param = null, IDbTransaction transaction = null, bool buffered = true, string splitOn = "Id", int? commandTimeout = null)
        {
            return Query<TFirst, TSecond, TThird, TFourth, TFifth, TReturn>(Dialect.GetQuery(name, sql), map, (object)param, transaction, buffered, splitOn, null);
        }

        public IEnumerable<dynamic> Query(string name, string sql, dynamic param = null, bool buffered = true)
        {
            return Query<dynamic>(Dialect.GetQuery(name, sql), (object)param, buffered);
        }

        public Dapper.SqlMapper.GridReader QueryMultiple(string name, string sql, dynamic param = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null)
        {
            return QueryMultiple(Dialect.GetQuery(name, sql), (object)param, transaction, commandTimeout, commandType);
        }
    }
}